import time
import pymysql
import os

def get_conn():
    """
    :return: 连接，游标
    """
    # 创建连接
    conn = pymysql.connect(host="127.0.0.1",
                           user="root",
                           password="root",
                           db="cov",
                           charset="utf8")
    # 创建游标
    cursor = conn.cursor()# 执行完毕返回的结果集默认以元组显示
    return conn, cursor

def close_conn(conn, cursor):
    cursor.close()
    conn.close()

def query(sql,*args):
    """
    封装通用查询
    :param sql:
    :param args:
    :return: 返回查询到的结果，((),(),)的形式
    """
    conn, cursor = get_conn()
    cursor.execute(sql,args)
    res = cursor.fetchall()
    close_conn(conn, cursor)
    return res

# 累计确诊
def get_summary_data():
    """
    :return: 返回大屏div id=c1 的数据
    """
    # 因为会更新多次数据，取时间戳最新的那组数据
    sql = """
    SELECT total,total-heal-dead,heal,dead from (
    select sum(confirm) total,
    (SELECT heal from history ORDER BY ds desc LIMIT 1) heal ,
      sum(dead) dead
    from details where update_time=(
      select update_time from details order by update_time desc limit 1)
    ) d;
    """

    sql_new = """
        SELECT total_new,domestic,abroad,dead from (
        select sum(confirm_add) total_new, 
				(select sum(confirm_add) from details where province not in ('台湾','香港','澳门')) domestic,
				(select sum(confirm_add) from details where city in ('境外输入')) abroad,
        (select dead_add from history where ds=(select ds from history order by ds desc limit 1)) dead
				from details
        ) d;
        """
    res = query(sql)
    res_new = query(sql_new)
    return res[0], res_new[0]

# 疫情分布图
def get_map_data():
    """
    :return:  返回各省数据
    """
    # 因为会更新多次数据，取时间戳最新的那组数据
    sql = "select province,sum(confirm_now) from details " \
          "where update_time=(select update_time from details " \
          "order by update_time desc limit 1) " \
          "group by province"
    res = query(sql)
    return res

# pie图
def get_pie_data():
    """
    :return:  返回各省数据
    """
    # 因为会更新多次数据，取时间戳最新的那组数据
    sql = "select province,sum(confirm_now) from details " \
          "where province not in ('台湾', '香港', '澳门') and update_time=(select update_time from details " \
          "order by update_time desc limit 1) " \
          "group by province"
    res = query(sql)
    return res

# 趋势图
def get_trend_data():
	sql = "select ds,confirm_add,suspect_add from history"
	res = query(sql)
	return res

# top图
def get_top_data():
    """
    :return:  返回剩余确诊人数前5名的省份
    """
    sql = 'SELECT province,confirm FROM ' \
          '(select province ,sum(confirm_now) as confirm from details  ' \
          'where province not in ("台湾", "香港", "澳门") and update_time=(select update_time from details ' \
          'order by update_time desc limit 1) ' \
          'group by province) as a ' \
          'ORDER BY confirm DESC LIMIT 5'
    res = query(sql)
    return res

# 风险播报
def get_risk_data():
    # 因为会更新多次数据，取时间戳最新的那组数据
	sql = "select end_update_time,province,city,county,address,type" \
          " from risk_area " \
          "where end_update_time=(select end_update_time " \
          "from risk_area " \
          "order by end_update_time desc limit 1) "
	res = query(sql)
	return res

#风险区搜索
def search_risk_data(key):
    # 因为会更新多次数据，取时间戳最新的那组数据
    sql = "select end_update_time,province,city,county,address,type" \
          " from risk_area " \
          "where province like %s and end_update_time=(select end_update_time " \
          "from risk_area " \
          "order by end_update_time desc limit 1) "
    res = query(sql, '%' + key + '%')
    return res

#自我检测
def self_check(uname, usex, uage, uphone, uaddress, problem, predict):
    conn, cls = get_conn()
    sql = "insert into mycheck values(null,%s,%s,%s,%s,%s,%s,%s)"
    row = cls.execute(sql, [uname, usex, uage, uphone, uaddress, problem, predict])
    conn.commit()
    close_conn(conn, cls)

#举报
def report_data(uname, usex, uage, uphone, uaddress, problem, patient, paddress):
    conn, cls = get_conn()
    sql = "insert into report values(null,%s,%s,%s,%s,%s,%s,%s,%s)"
    row = cls.execute(sql, [uname, usex, uage, uphone, uaddress, problem, patient, paddress])
    conn.commit()
    close_conn(conn, cls)

#谣言数据
def get_rumor_data():
    sql = 'select content , discern, src from newsdiscern order by id desc limit 30'
    res = query(sql)
    return res

#谣言搜索
def search_rumor_data(key):
    sql = 'select content , discern, src from newsdiscern where content like %s order by id desc limit 30'
    res = query(sql, '%'+key+'%')
    print(res)
    return res
#往期视频
def get_video_data(filePath):       #引入os
    file_name = list()
    for i in os.listdir(filePath):        #获取filePath路径下所有文件名,用os.listdir来获取总文件夹下面的全部内容
        data_collect = ''.join(i)        #文件名字符串格式
        file_name.append(data_collect)        #将文件名作为列表元素填入
    return(file_name)        #返回列表

#省市下转
def get_province_data():
    sql = "select city,confirm_now from details " \
          "where city not in ('地区待确认','境外输入') and update_time=(select update_time from details " \
          "order by update_time desc limit 1) "
    res = query(sql)
    return res

#热搜
def get_hotSearch_data():
    sql = "select content from hotsearch order by id desc limit 20"
    res = query(sql)
    return res